import sys

import openpyxl
from PyQt5.QtWidgets import QMainWindow, QApplication, QGraphicsObject
from untitled import Ui_Form
import threading
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, Alignment
from PyQt5.QtWidgets import QFileDialog, QGraphicsObject
import datetime


class tebles(QMainWindow, Ui_Form, QGraphicsObject):

    def __init__(self):
        super().__init__()
        self.setupUi(self)
        self.pushButton.clicked.connect(self.open_file)
        self.pushButton_7.clicked.connect(self.biaoge_pl)
        self.pushButton_8.clicked.connect(self.Ns_school_pl)
        self.pushButton_4.clicked.connect(self.open_input_file)
        self.pushButton_5.clicked.connect(self.open_pipei_file)
        self.pushButton_6.clicked.connect(lambda: self.thread_j(self.pipei_luru))
        self.pushButton_2.clicked.connect(lambda: self.thread_j(self.biaoge))
        self.pushButton_3.clicked.connect(lambda: self.thread_j(self.Ns_school))

    def Qtext_A(self, message):
        self.textBrowser.append(message)

    def thread_j(self, massage):
        t = threading.Thread(target=massage, daemon=True)
        t.start()

    def biaoge(self):
        workbook1 = Workbook()
        # 实例化三号输入框内容
        c1 = self.lineEdit_3.text()
        if self.lineEdit_3.text() == '':
            # 实例化二号输入框内容
            a1 = int(self.lineEdit.text()) + 1
            # 实例化一号号输入框内容
            b1 = int(self.lineEdit_2.text()) + 1
            for i in range(1, a1):
                for y in range(1, b1):
                    sheet = workbook1.create_sheet(f'{self.lineEdit_5.text()}{i}年级{y}班')
                    self.Qtext_A(f'创建{i}年级{y}班完成')
                    sheet.merge_cells("I1:K1")
                    sheet.merge_cells("L1:X1")
                    sheet.merge_cells("Y1:Z1")
                    sheet.merge_cells("AA1:AD1")
                    sheet.merge_cells("AE1:AH1")
                    sheet.merge_cells("AI1:AL1")
                    sheet.merge_cells("AM1:AN1")
                    sheet.merge_cells("AO1:AS1")
                    sheet['A1'].alignment = Alignment(horizontal="center")
                    sheet['I1'] = '内科'
                    sheet['I1'].alignment = Alignment(horizontal="center")
                    sheet['L1'] = '外科体检结果'
                    sheet['L1'].alignment = Alignment(horizontal="center")
                    sheet['Y1'] = '矫正视力'
                    sheet['Y1'].alignment = Alignment(horizontal="center")
                    sheet['AA1'] = '裸视力'
                    sheet['AA1'].alignment = Alignment(horizontal="center")
                    sheet['AE1'] = '左眼'
                    sheet['AE1'].alignment = Alignment(horizontal="center")
                    sheet['AI1'] = '右眼'
                    sheet['AI1'].alignment = Alignment(horizontal="center")
                    sheet['AM1'] = '眼科'
                    sheet['AM1'].alignment = Alignment(horizontal="center")
                    sheet['AO1'] = '五官科'
                    sheet['AO1'].alignment = Alignment(horizontal="center")
                    sheet['A2'] = '录入者'
                    sheet['A2'].alignment = Alignment(horizontal="center")
                    sheet['B2'] = '班级'
                    sheet['B2'].alignment = Alignment(horizontal="center")
                    sheet['C2'] = '序号'
                    sheet['C2'].alignment = Alignment(horizontal="center")
                    sheet['D2'] = '姓名'
                    sheet['D2'].alignment = Alignment(horizontal="center")
                    sheet['E2'] = '性别'
                    sheet['E2'].alignment = Alignment(horizontal="center")
                    sheet['F2'] = '身份证号'
                    sheet['F2'].alignment = Alignment(horizontal="center")
                    sheet['G2'] = '到检情况'
                    sheet['G2'].alignment = Alignment(horizontal="center")
                    sheet['H2'] = '年龄'
                    sheet['H2'].alignment = Alignment(horizontal="center")
                    sheet['I2'] = '心'
                    sheet['I2'].alignment = Alignment(horizontal="center")
                    sheet['J2'] = '肺'
                    sheet['J2'].alignment = Alignment(horizontal="center")
                    sheet['K2'] = '肝/脾'
                    sheet['K2'].alignment = Alignment(horizontal="center")
                    sheet['L2'] = '身高(米)'
                    sheet['L2'].alignment = Alignment(horizontal="center")
                    sheet['M2'] = '体重(KG)'
                    sheet['M2'].alignment = Alignment(horizontal="center")
                    sheet['N2'] = 'BMI'
                    sheet['N2'].alignment = Alignment(horizontal="center")
                    sheet['O2'] = 'BMI修正'
                    sheet['O2'].alignment = Alignment(horizontal="center")
                    sheet['P2'] = 'BMI比对结果'
                    sheet['P2'].alignment = Alignment(horizontal="center")
                    sheet['Q2'] = '身高比对结果'
                    sheet['Q2'].alignment = Alignment(horizontal="center")
                    sheet['R2'] = '头部'
                    sheet['R2'].alignment = Alignment(horizontal="center")
                    sheet['S2'] = '颈部'
                    sheet['S2'].alignment = Alignment(horizontal="center")
                    sheet['T2'] = '胸部'
                    sheet['T2'].alignment = Alignment(horizontal="center")
                    sheet['U2'] = '脊柱'
                    sheet['U2'].alignment = Alignment(horizontal="center")
                    sheet['V2'] = '四肢'
                    sheet['V2'].alignment = Alignment(horizontal="center")
                    sheet['W2'] = '皮肤'
                    sheet['W2'].alignment = Alignment(horizontal="center")
                    sheet['X2'] = '淋巴结'
                    sheet['X2'].alignment = Alignment(horizontal="center")
                    sheet['Y2'] = '左'
                    sheet['Y2'].alignment = Alignment(horizontal="center")
                    sheet['Z2'] = '右'
                    sheet['Z2'].alignment = Alignment(horizontal="center")
                    sheet['AA2'] = '左'
                    sheet['AA2'].alignment = Alignment(horizontal="center")
                    sheet['AB2'] = '右'
                    sheet['AB2'].alignment = Alignment(horizontal="center")
                    sheet['AC2'] = '屈光'
                    sheet['AC2'].alignment = Alignment(horizontal="center")
                    sheet['AD2'] = '视力评价'
                    sheet['AD2'].alignment = Alignment(horizontal="center")
                    sheet['AE2'] = '球镜'
                    sheet['AE2'].alignment = Alignment(horizontal="center")
                    sheet['AF2'] = '柱镜'
                    sheet['AF2'].alignment = Alignment(horizontal="center")
                    sheet['AG2'] = '轴位'
                    sheet['AG2'].alignment = Alignment(horizontal="center")
                    sheet['AH2'] = '佩戴OK镜'
                    sheet['AH2'].alignment = Alignment(horizontal="center")
                    sheet['AI2'] = '球镜'
                    sheet['AI2'].alignment = Alignment(horizontal="center")
                    sheet['AJ2'] = '柱镜'
                    sheet['AJ2'].alignment = Alignment(horizontal="center")
                    sheet['AK2'] = '轴位'
                    sheet['AK2'].alignment = Alignment(horizontal="center")
                    sheet['AL2'] = '佩戴OK镜'
                    sheet['AL2'].alignment = Alignment(horizontal="center")
                    sheet['AM2'] = '结膜炎'
                    sheet['AM2'].alignment = Alignment(horizontal="center")
                    sheet['AN2'] = '沙眼'
                    sheet['AN2'].alignment = Alignment(horizontal="center")
                    sheet['AO2'] = '耳'
                    sheet['AO2'].alignment = Alignment(horizontal="center")
                    sheet['AP2'] = '鼻'
                    sheet['AP2'].alignment = Alignment(horizontal="center")
                    sheet['AQ2'] = '喉/扁桃体'
                    sheet['AQ2'].alignment = Alignment(horizontal="center")
                    sheet['AR2'] = '龋齿'
                    sheet['AR2'].alignment = Alignment(horizontal="center")
                    sheet['AS2'] = '牙周'
                    sheet['AS2'].alignment = Alignment(horizontal="center")
                    sheet['AT2'] = '肺活量'
                    sheet['AT2'].alignment = Alignment(horizontal="center")
            sheet1 = workbook1['Sheet']
            workbook1.remove(sheet1)
            workbook1.save(filename=f'文件/{self.lineEdit_4.text()}.xlsx')
            return self.Qtext_A(f'{self.lineEdit_4.text()}文件创建完成')
        elif self.lineEdit.text() == '' and self.lineEdit_2.text() == '':
            list_1 = c1.split(',')
            for i in list_1:
                sheet = workbook1.create_sheet(f'{i}')
                self.Qtext_A(f'创建{i}完成')
                sheet.merge_cells("I1:K1")
                sheet.merge_cells("L1:X1")
                sheet.merge_cells("Y1:Z1")
                sheet.merge_cells("AA1:AD1")
                sheet.merge_cells("AE1:AH1")
                sheet.merge_cells("AI1:AL1")
                sheet.merge_cells("AM1:AN1")
                sheet.merge_cells("AO1:AS1")
                sheet['A1'].alignment = Alignment(horizontal="center")
                sheet['I1'] = '内科'
                sheet['I1'].alignment = Alignment(horizontal="center")
                sheet['L1'] = '外科体检结果'
                sheet['L1'].alignment = Alignment(horizontal="center")
                sheet['Y1'] = '矫正视力'
                sheet['Y1'].alignment = Alignment(horizontal="center")
                sheet['AA1'] = '裸视力'
                sheet['AA1'].alignment = Alignment(horizontal="center")
                sheet['AE1'] = '左眼'
                sheet['AE1'].alignment = Alignment(horizontal="center")
                sheet['AI1'] = '右眼'
                sheet['AI1'].alignment = Alignment(horizontal="center")
                sheet['AM1'] = '眼科'
                sheet['AM1'].alignment = Alignment(horizontal="center")
                sheet['AO1'] = '五官科'
                sheet['AO1'].alignment = Alignment(horizontal="center")
                sheet['A2'] = '录入者'
                sheet['A2'].alignment = Alignment(horizontal="center")
                sheet['B2'] = '班级'
                sheet['B2'].alignment = Alignment(horizontal="center")
                sheet['C2'] = '序号'
                sheet['C2'].alignment = Alignment(horizontal="center")
                sheet['D2'] = '姓名'
                sheet['D2'].alignment = Alignment(horizontal="center")
                sheet['E2'] = '性别'
                sheet['E2'].alignment = Alignment(horizontal="center")
                sheet['F2'] = '身份证号'
                sheet['F2'].alignment = Alignment(horizontal="center")
                sheet['G2'] = '到检情况'
                sheet['G2'].alignment = Alignment(horizontal="center")
                sheet['H2'] = '年龄'
                sheet['H2'].alignment = Alignment(horizontal="center")
                sheet['I2'] = '心'
                sheet['I2'].alignment = Alignment(horizontal="center")
                sheet['J2'] = '肺'
                sheet['J2'].alignment = Alignment(horizontal="center")
                sheet['K2'] = '肝/脾'
                sheet['K2'].alignment = Alignment(horizontal="center")
                sheet['L2'] = '身高(米)'
                sheet['L2'].alignment = Alignment(horizontal="center")
                sheet['M2'] = '体重(KG)'
                sheet['M2'].alignment = Alignment(horizontal="center")
                sheet['N2'] = 'BMI'
                sheet['N2'].alignment = Alignment(horizontal="center")
                sheet['O2'] = 'BMI修正'
                sheet['O2'].alignment = Alignment(horizontal="center")
                sheet['P2'] = 'BMI比对结果'
                sheet['P2'].alignment = Alignment(horizontal="center")
                sheet['Q2'] = '身高比对结果'
                sheet['Q2'].alignment = Alignment(horizontal="center")
                sheet['R2'] = '头部'
                sheet['R2'].alignment = Alignment(horizontal="center")
                sheet['S2'] = '颈部'
                sheet['S2'].alignment = Alignment(horizontal="center")
                sheet['T2'] = '胸部'
                sheet['T2'].alignment = Alignment(horizontal="center")
                sheet['U2'] = '脊柱'
                sheet['U2'].alignment = Alignment(horizontal="center")
                sheet['V2'] = '四肢'
                sheet['V2'].alignment = Alignment(horizontal="center")
                sheet['W2'] = '皮肤'
                sheet['W2'].alignment = Alignment(horizontal="center")
                sheet['X2'] = '淋巴结'
                sheet['X2'].alignment = Alignment(horizontal="center")
                sheet['Y2'] = '左'
                sheet['Y2'].alignment = Alignment(horizontal="center")
                sheet['Z2'] = '右'
                sheet['Z2'].alignment = Alignment(horizontal="center")
                sheet['AA2'] = '左'
                sheet['AA2'].alignment = Alignment(horizontal="center")
                sheet['AB2'] = '右'
                sheet['AB2'].alignment = Alignment(horizontal="center")
                sheet['AC2'] = '屈光'
                sheet['AC2'].alignment = Alignment(horizontal="center")
                sheet['AD2'] = '视力评价'
                sheet['AD2'].alignment = Alignment(horizontal="center")
                sheet['AE2'] = '球镜'
                sheet['AE2'].alignment = Alignment(horizontal="center")
                sheet['AF2'] = '柱镜'
                sheet['AF2'].alignment = Alignment(horizontal="center")
                sheet['AG2'] = '轴位'
                sheet['AG2'].alignment = Alignment(horizontal="center")
                sheet['AH2'] = '佩戴OK镜'
                sheet['AH2'].alignment = Alignment(horizontal="center")
                sheet['AI2'] = '球镜'
                sheet['AI2'].alignment = Alignment(horizontal="center")
                sheet['AJ2'] = '柱镜'
                sheet['AJ2'].alignment = Alignment(horizontal="center")
                sheet['AK2'] = '轴位'
                sheet['AK2'].alignment = Alignment(horizontal="center")
                sheet['AL2'] = '佩戴OK镜'
                sheet['AL2'].alignment = Alignment(horizontal="center")
                sheet['AM2'] = '结膜炎'
                sheet['AM2'].alignment = Alignment(horizontal="center")
                sheet['AN2'] = '沙眼'
                sheet['AN2'].alignment = Alignment(horizontal="center")
                sheet['AO2'] = '耳'
                sheet['AO2'].alignment = Alignment(horizontal="center")
                sheet['AP2'] = '鼻'
                sheet['AP2'].alignment = Alignment(horizontal="center")
                sheet['AQ2'] = '喉/扁桃体'
                sheet['AQ2'].alignment = Alignment(horizontal="center")
                sheet['AR2'] = '龋齿'
                sheet['AR2'].alignment = Alignment(horizontal="center")
                sheet['AS2'] = '牙周'
                sheet['AS2'].alignment = Alignment(horizontal="center")
                sheet['AT2'] = '肺活量'
                sheet['AT2'].alignment = Alignment(horizontal="center")
            sheet1 = workbook1['Sheet']
            workbook1.remove(sheet1)
            workbook1.save(filename=f'文件/{self.lineEdit_4.text()}.xlsx')
            return self.Qtext_A(f'{self.lineEdit_4.text()}文件创建完成')
        else:
            a1 = int(self.lineEdit.text()) + 1
            b1 = int(self.lineEdit_2.text()) + 1
            for i in range(1, a1):
                for y in range(1, b1):
                    sheet = workbook1.create_sheet(f'{self.lineEdit_5.text()}{i}年级{y}班')
                    self.Qtext_A(f'创建{i}年级{y}班完成')
                    sheet.merge_cells("I1:K1")
                    sheet.merge_cells("L1:X1")
                    sheet.merge_cells("Y1:Z1")
                    sheet.merge_cells("AA1:AD1")
                    sheet.merge_cells("AE1:AH1")
                    sheet.merge_cells("AI1:AL1")
                    sheet.merge_cells("AM1:AN1")
                    sheet.merge_cells("AO1:AS1")
                    sheet['A1'].alignment = Alignment(horizontal="center")
                    sheet['I1'] = '内科'
                    sheet['I1'].alignment = Alignment(horizontal="center")
                    sheet['L1'] = '外科体检结果'
                    sheet['L1'].alignment = Alignment(horizontal="center")
                    sheet['Y1'] = '矫正视力'
                    sheet['Y1'].alignment = Alignment(horizontal="center")
                    sheet['AA1'] = '裸视力'
                    sheet['AA1'].alignment = Alignment(horizontal="center")
                    sheet['AE1'] = '左眼'
                    sheet['AE1'].alignment = Alignment(horizontal="center")
                    sheet['AI1'] = '右眼'
                    sheet['AI1'].alignment = Alignment(horizontal="center")
                    sheet['AM1'] = '眼科'
                    sheet['AM1'].alignment = Alignment(horizontal="center")
                    sheet['AO1'] = '五官科'
                    sheet['AO1'].alignment = Alignment(horizontal="center")
                    sheet['A2'] = '录入者'
                    sheet['A2'].alignment = Alignment(horizontal="center")
                    sheet['B2'] = '班级'
                    sheet['B2'].alignment = Alignment(horizontal="center")
                    sheet['C2'] = '序号'
                    sheet['C2'].alignment = Alignment(horizontal="center")
                    sheet['D2'] = '姓名'
                    sheet['D2'].alignment = Alignment(horizontal="center")
                    sheet['E2'] = '性别'
                    sheet['E2'].alignment = Alignment(horizontal="center")
                    sheet['F2'] = '身份证号'
                    sheet['F2'].alignment = Alignment(horizontal="center")
                    sheet['G2'] = '到检情况'
                    sheet['G2'].alignment = Alignment(horizontal="center")
                    sheet['H2'] = '年龄'
                    sheet['H2'].alignment = Alignment(horizontal="center")
                    sheet['I2'] = '心'
                    sheet['I2'].alignment = Alignment(horizontal="center")
                    sheet['J2'] = '肺'
                    sheet['J2'].alignment = Alignment(horizontal="center")
                    sheet['K2'] = '肝/脾'
                    sheet['K2'].alignment = Alignment(horizontal="center")
                    sheet['L2'] = '身高(米)'
                    sheet['L2'].alignment = Alignment(horizontal="center")
                    sheet['M2'] = '体重(KG)'
                    sheet['M2'].alignment = Alignment(horizontal="center")
                    sheet['N2'] = 'BMI'
                    sheet['N2'].alignment = Alignment(horizontal="center")
                    sheet['O2'] = 'BMI修正'
                    sheet['O2'].alignment = Alignment(horizontal="center")
                    sheet['P2'] = 'BMI比对结果'
                    sheet['P2'].alignment = Alignment(horizontal="center")
                    sheet['Q2'] = '身高比对结果'
                    sheet['Q2'].alignment = Alignment(horizontal="center")
                    sheet['R2'] = '头部'
                    sheet['R2'].alignment = Alignment(horizontal="center")
                    sheet['S2'] = '颈部'
                    sheet['S2'].alignment = Alignment(horizontal="center")
                    sheet['T2'] = '胸部'
                    sheet['T2'].alignment = Alignment(horizontal="center")
                    sheet['U2'] = '脊柱'
                    sheet['U2'].alignment = Alignment(horizontal="center")
                    sheet['V2'] = '四肢'
                    sheet['V2'].alignment = Alignment(horizontal="center")
                    sheet['W2'] = '皮肤'
                    sheet['W2'].alignment = Alignment(horizontal="center")
                    sheet['X2'] = '淋巴结'
                    sheet['X2'].alignment = Alignment(horizontal="center")
                    sheet['Y2'] = '左'
                    sheet['Y2'].alignment = Alignment(horizontal="center")
                    sheet['Z2'] = '右'
                    sheet['Z2'].alignment = Alignment(horizontal="center")
                    sheet['AA2'] = '左'
                    sheet['AA2'].alignment = Alignment(horizontal="center")
                    sheet['AB2'] = '右'
                    sheet['AB2'].alignment = Alignment(horizontal="center")
                    sheet['AC2'] = '屈光'
                    sheet['AC2'].alignment = Alignment(horizontal="center")
                    sheet['AD2'] = '视力评价'
                    sheet['AD2'].alignment = Alignment(horizontal="center")
                    sheet['AE2'] = '球镜'
                    sheet['AE2'].alignment = Alignment(horizontal="center")
                    sheet['AF2'] = '柱镜'
                    sheet['AF2'].alignment = Alignment(horizontal="center")
                    sheet['AG2'] = '轴位'
                    sheet['AG2'].alignment = Alignment(horizontal="center")
                    sheet['AH2'] = '佩戴OK镜'
                    sheet['AH2'].alignment = Alignment(horizontal="center")
                    sheet['AI2'] = '球镜'
                    sheet['AI2'].alignment = Alignment(horizontal="center")
                    sheet['AJ2'] = '柱镜'
                    sheet['AJ2'].alignment = Alignment(horizontal="center")
                    sheet['AK2'] = '轴位'
                    sheet['AK2'].alignment = Alignment(horizontal="center")
                    sheet['AL2'] = '佩戴OK镜'
                    sheet['AL2'].alignment = Alignment(horizontal="center")
                    sheet['AM2'] = '结膜炎'
                    sheet['AM2'].alignment = Alignment(horizontal="center")
                    sheet['AN2'] = '沙眼'
                    sheet['AN2'].alignment = Alignment(horizontal="center")
                    sheet['AO2'] = '耳'
                    sheet['AO2'].alignment = Alignment(horizontal="center")
                    sheet['AP2'] = '鼻'
                    sheet['AP2'].alignment = Alignment(horizontal="center")
                    sheet['AQ2'] = '喉/扁桃体'
                    sheet['AQ2'].alignment = Alignment(horizontal="center")
                    sheet['AR2'] = '龋齿'
                    sheet['AR2'].alignment = Alignment(horizontal="center")
                    sheet['AS2'] = '牙周'
                    sheet['AS2'].alignment = Alignment(horizontal="center")
                    sheet['AT2'] = '肺活量'
                    sheet['AT2'].alignment = Alignment(horizontal="center")
            list_1 = c1.split(',')
            for i in list_1:
                sheet = workbook1.create_sheet(f'{i}')
                self.Qtext_A(f'创建{i}完成，开始处理中！')
                sheet.merge_cells("I1:K1")
                sheet.merge_cells("L1:X1")
                sheet.merge_cells("Y1:Z1")
                sheet.merge_cells("AA1:AD1")
                sheet.merge_cells("AE1:AH1")
                sheet.merge_cells("AI1:AL1")
                sheet.merge_cells("AM1:AN1")
                sheet.merge_cells("AO1:AS1")
                sheet['A1'].alignment = Alignment(horizontal="center")
                sheet['I1'] = '内科'
                sheet['I1'].alignment = Alignment(horizontal="center")
                sheet['L1'] = '外科体检结果'
                sheet['L1'].alignment = Alignment(horizontal="center")
                sheet['Y1'] = '矫正视力'
                sheet['Y1'].alignment = Alignment(horizontal="center")
                sheet['AA1'] = '裸视力'
                sheet['AA1'].alignment = Alignment(horizontal="center")
                sheet['AE1'] = '左眼'
                sheet['AE1'].alignment = Alignment(horizontal="center")
                sheet['AI1'] = '右眼'
                sheet['AI1'].alignment = Alignment(horizontal="center")
                sheet['AM1'] = '眼科'
                sheet['AM1'].alignment = Alignment(horizontal="center")
                sheet['AO1'] = '五官科'
                sheet['AO1'].alignment = Alignment(horizontal="center")
                sheet['A2'] = '录入者'
                sheet['A2'].alignment = Alignment(horizontal="center")
                sheet['B2'] = '班级'
                sheet['B2'].alignment = Alignment(horizontal="center")
                sheet['C2'] = '序号'
                sheet['C2'].alignment = Alignment(horizontal="center")
                sheet['D2'] = '姓名'
                sheet['D2'].alignment = Alignment(horizontal="center")
                sheet['E2'] = '性别'
                sheet['E2'].alignment = Alignment(horizontal="center")
                sheet['F2'] = '身份证号'
                sheet['F2'].alignment = Alignment(horizontal="center")
                sheet['G2'] = '到检情况'
                sheet['G2'].alignment = Alignment(horizontal="center")
                sheet['H2'] = '年龄'
                sheet['H2'].alignment = Alignment(horizontal="center")
                sheet['I2'] = '心'
                sheet['I2'].alignment = Alignment(horizontal="center")
                sheet['J2'] = '肺'
                sheet['J2'].alignment = Alignment(horizontal="center")
                sheet['K2'] = '肝/脾'
                sheet['K2'].alignment = Alignment(horizontal="center")
                sheet['L2'] = '身高(米)'
                sheet['L2'].alignment = Alignment(horizontal="center")
                sheet['M2'] = '体重(KG)'
                sheet['M2'].alignment = Alignment(horizontal="center")
                sheet['N2'] = 'BMI'
                sheet['N2'].alignment = Alignment(horizontal="center")
                sheet['O2'] = 'BMI修正'
                sheet['O2'].alignment = Alignment(horizontal="center")
                sheet['P2'] = 'BMI比对结果'
                sheet['P2'].alignment = Alignment(horizontal="center")
                sheet['Q2'] = '身高比对结果'
                sheet['Q2'].alignment = Alignment(horizontal="center")
                sheet['R2'] = '头部'
                sheet['R2'].alignment = Alignment(horizontal="center")
                sheet['S2'] = '颈部'
                sheet['S2'].alignment = Alignment(horizontal="center")
                sheet['T2'] = '胸部'
                sheet['T2'].alignment = Alignment(horizontal="center")
                sheet['U2'] = '脊柱'
                sheet['U2'].alignment = Alignment(horizontal="center")
                sheet['V2'] = '四肢'
                sheet['V2'].alignment = Alignment(horizontal="center")
                sheet['W2'] = '皮肤'
                sheet['W2'].alignment = Alignment(horizontal="center")
                sheet['X2'] = '淋巴结'
                sheet['X2'].alignment = Alignment(horizontal="center")
                sheet['Y2'] = '左'
                sheet['Y2'].alignment = Alignment(horizontal="center")
                sheet['Z2'] = '右'
                sheet['Z2'].alignment = Alignment(horizontal="center")
                sheet['AA2'] = '左'
                sheet['AA2'].alignment = Alignment(horizontal="center")
                sheet['AB2'] = '右'
                sheet['AB2'].alignment = Alignment(horizontal="center")
                sheet['AC2'] = '屈光'
                sheet['AC2'].alignment = Alignment(horizontal="center")
                sheet['AD2'] = '视力评价'
                sheet['AD2'].alignment = Alignment(horizontal="center")
                sheet['AE2'] = '球镜'
                sheet['AE2'].alignment = Alignment(horizontal="center")
                sheet['AF2'] = '柱镜'
                sheet['AF2'].alignment = Alignment(horizontal="center")
                sheet['AG2'] = '轴位'
                sheet['AG2'].alignment = Alignment(horizontal="center")
                sheet['AH2'] = '佩戴OK镜'
                sheet['AH2'].alignment = Alignment(horizontal="center")
                sheet['AI2'] = '球镜'
                sheet['AI2'].alignment = Alignment(horizontal="center")
                sheet['AJ2'] = '柱镜'
                sheet['AJ2'].alignment = Alignment(horizontal="center")
                sheet['AK2'] = '轴位'
                sheet['AK2'].alignment = Alignment(horizontal="center")
                sheet['AL2'] = '佩戴OK镜'
                sheet['AL2'].alignment = Alignment(horizontal="center")
                sheet['AM2'] = '结膜炎'
                sheet['AM2'].alignment = Alignment(horizontal="center")
                sheet['AN2'] = '沙眼'
                sheet['AN2'].alignment = Alignment(horizontal="center")
                sheet['AO2'] = '耳'
                sheet['AO2'].alignment = Alignment(horizontal="center")
                sheet['AP2'] = '鼻'
                sheet['AP2'].alignment = Alignment(horizontal="center")
                sheet['AQ2'] = '喉/扁桃体'
                sheet['AQ2'].alignment = Alignment(horizontal="center")
                sheet['AR2'] = '龋齿'
                sheet['AR2'].alignment = Alignment(horizontal="center")
                sheet['AS2'] = '牙周'
                sheet['AS2'].alignment = Alignment(horizontal="center")
                sheet['AT2'] = '肺活量'
                sheet['AT2'].alignment = Alignment(horizontal="center")
            sheet1 = workbook1['Sheet']
            workbook1.remove(sheet1)
            workbook1.save(filename=f'文件/{self.lineEdit_4.text()}.xlsx')
            return self.Qtext_A(f'{self.lineEdit_4.text()}文件创建完成')

    def Ns_school(self):
        #                   水平对齐              垂直对齐            自动换行
        align = Alignment(horizontal="center", vertical="center", wrap_text=True)
        font = Font(name="宋体", size=9)
        wb = Workbook()
        c1 = self.lineEdit_3.text()
        if self.lineEdit_3.text() == '':
            a1 = int(self.lineEdit.text()) + 1
            b1 = int(self.lineEdit_2.text()) + 1
            for i in range(1, a1):
                for y in range(1, b1):
                    sheet = wb.create_sheet(f'{self.lineEdit_5.text()}{i}年级{y}班')
                    self.Qtext_A(f'创建{i}年级{y}班完成')
                    sheet['B1'] = '学年'
                    sheet['B1'].alignment = align

                    sheet['C1'] = '第一学年'
                    sheet['C1'].alignment = align

                    sheet['A2'] = '序号'
                    sheet['A2'].alignment = align
                    sheet.merge_cells('A2:A4')

                    sheet['B2'] = '年级班级'
                    sheet.merge_cells('B2:B4')
                    sheet['B2'].alignment = align

                    sheet['C2'] = '学生姓名'
                    sheet.merge_cells('C2:C4')
                    sheet['C2'].alignment = align

                    sheet['D2'] = '身份证号码'
                    sheet.merge_cells('D2:D4')
                    sheet['D2'].alignment = align

                    sheet['E2'] = '性别'
                    sheet.merge_cells('E2:E4')
                    sheet['E2'].alignment = align

                    sheet['F2'] = '年龄'
                    sheet.merge_cells('F2:F4')
                    sheet['F2'].alignment = align

                    sheet['G2'] = '到检情况'
                    sheet.merge_cells('G2:G4')
                    sheet['G2'].alignment = align

                    sheet['H2'] = '学籍号'
                    sheet.merge_cells('H2:H4')
                    sheet['H2'].alignment = align

                    sheet['I2'] = '查期'
                    sheet.merge_cells('I2:I4')
                    sheet['I2'].alignment = align

                    sheet['J2'] = '发育及营养状况'
                    sheet.merge_cells('J2:J4')
                    sheet['J2'].alignment = align

                    sheet['K2'] = '身高(cm)'
                    sheet.merge_cells('K2:K4')
                    sheet['K2'].alignment = align

                    sheet['L2'] = '体重(kg)'
                    sheet.merge_cells('L2:L4')
                    sheet['L2'].alignment = align

                    sheet['M2'] = '胸围(cm)'
                    sheet.merge_cells('M2:M4')
                    sheet['M2'].alignment = align

                    sheet['N2'] = '肺活量(ml)'
                    sheet.merge_cells('N2:N4')
                    sheet['N2'].alignment = align

                    sheet['O2'] = '血压(mmhg)'
                    sheet.merge_cells('O2:O4')
                    sheet['O2'].alignment = align

                    sheet['P2'] = '脉搏(次/分)'
                    sheet.merge_cells('P2:P4')
                    sheet['P2'].alignment = align

                    sheet['Q2'] = '视力'
                    sheet.merge_cells('Q2:V2')
                    sheet['Q2'].alignment = align

                    sheet['Q3'] = '左'
                    sheet.merge_cells('Q3:S3')
                    sheet['Q3'].alignment = align

                    sheet['T3'] = '右'
                    sheet.merge_cells('T3:V3')
                    sheet['T3'].alignment = align

                    sheet['Q4'] = '裸眼视力'
                    sheet['Q4'].alignment = align

                    sheet['R4'] = '矫正视力'
                    sheet['R4'].alignment = align

                    sheet['S4'] = '矫正度数'
                    sheet['S4'].alignment = align

                    sheet['T4'] = '裸眼视力'
                    sheet['T4'].alignment = align

                    sheet['U4'] = '矫正视力'
                    sheet['U4'].alignment = align

                    sheet['V4'] = '矫正度数'
                    sheet['V4'].alignment = align

                    sheet['W2'] = '听力'
                    sheet.merge_cells('W2:X2')
                    sheet['W2'].alignment = align

                    sheet['W3'] = '左'
                    sheet['W3'].alignment = align

                    sheet['X3'] = '右'
                    sheet['X3'].alignment = align

                    sheet['Y2'] = '沙眼'
                    sheet.merge_cells('Y2:Y4')
                    sheet['Y2'].alignment = align

                    sheet['Z2'] = '色觉'
                    sheet.merge_cells('Z2:Z4')
                    sheet['Z2'].alignment = align

                    sheet['AA2'] = '鼻病'
                    sheet.merge_cells('AA2:AA4')
                    sheet['AA2'].alignment = align

                    sheet['AB2'] = '口腔'
                    sheet.merge_cells('AB2:AB4')
                    sheet['AB2'].alignment = align

                    sheet['AC2'] = '心脏'
                    sheet.merge_cells('AC2:AC4')
                    sheet['AC2'].alignment = align

                    sheet['AD2'] = '肺'
                    sheet.merge_cells('AD2:AD4')
                    sheet['AD2'].alignment = align

                    sheet['AE2'] = '肝脾'
                    sheet.merge_cells('AE2:AE4')
                    sheet['AE2'].alignment = align

                    sheet['AF2'] = '胃肠'
                    sheet.merge_cells('AF2:AF4')
                    sheet['AF2'].alignment = align

                    sheet['AG2'] = '脊柱'
                    sheet.merge_cells('AG2:AG4')
                    sheet['AG2'].alignment = align

                    sheet['AH2'] = '四肢'
                    sheet.merge_cells('AH2:AH4')
                    sheet['AH2'].alignment = align

                    sheet['AI2'] = '平足'
                    sheet.merge_cells('AI2:AI4')
                    sheet['AI2'].alignment = align

                    sheet['AJ2'] = '皮肤'
                    sheet.merge_cells('AJ2:AJ4')
                    sheet['AJ2'].alignment = align

                    sheet['AK2'] = '传染病'
                    sheet.merge_cells('AK2:AK4')
                    sheet['AK2'].alignment = align

                    sheet['AL2'] = '胸透'
                    sheet.merge_cells('AL2:AL4')
                    sheet['AL2'].alignment = align

                    sheet['AM2'] = '既往病史'
                    sheet.merge_cells('AM2:AM4')
                    sheet['AM2'].alignment = align

                    sheet['AN2'] = '医院名或校医名'
                    sheet.merge_cells('AN2:AN4')
                    sheet['AN2'].alignment = align

                    sheet['AO2'] = '头部'
                    sheet.merge_cells('AO2:AO4')
                    sheet['AO2'].alignment = align

                    sheet['AP2'] = '颈部'
                    sheet.merge_cells('AP2:AP4')
                    sheet['AP2'].alignment = align

                    sheet['AQ2'] = '胸部'
                    sheet.merge_cells('AQ2:AQ4')
                    sheet['AQ2'].alignment = align

                    sheet['AR2'] = '淋巴'
                    sheet.merge_cells('AR2:AR4')
                    sheet['AR2'].alignment = align

                    sheet['AS2'] = '扁桃体'
                    sheet.merge_cells('AS2:AS4')
                    sheet['AS2'].alignment = align

                    sheet['AT2'] = 'BMI'
                    sheet.merge_cells('AT2:AT4')
                    sheet['AT2'].alignment = align

                    sheet['AU2'] = 'BMI对比结果'
                    sheet.merge_cells('AU2:AU4')
                    sheet['AU2'].alignment = align

                    sheet['AV2'] = '身高对比结果'
                    sheet.merge_cells('AV2:AV4')
                    sheet['AV2'].alignment = align

                    sheet['AW2'] = '视力对比结果'
                    sheet.merge_cells('AW2:AW4')
                    sheet['AW2'].alignment = align

                    sheet['AX2'] = '耳异常'
                    sheet.merge_cells('AX2:AX4')
                    sheet['AX2'].alignment = align

                    sheet['AY2'] = '龋齿'
                    sheet.merge_cells('AY2:AY4')
                    sheet['AY2'].alignment = align

                    sheet.row_dimensions[2].height = 19.5
                    sheet.column_dimensions['A'].width = 8.38
                    sheet.column_dimensions['B'].width = 11.88
                    sheet.column_dimensions['C'].width = 14
                    sheet.column_dimensions['D'].width = 20.13
                    sheet.column_dimensions['E'].width = 15.63
                    sheet.column_dimensions['F'].width = 7
                    sheet.column_dimensions['G'].width = 7.5
                    sheet.column_dimensions['H'].width = 7.5
                    sheet.column_dimensions['I'].width = 7.5
                    sheet.column_dimensions['J'].width = 7.5
                    sheet.column_dimensions['K'].width = 7.5
                    sheet.column_dimensions['L'].width = 11
                    sheet.column_dimensions['M'].width = 8.38
                    sheet.column_dimensions['N'].width = 8.38
                    sheet.column_dimensions['O'].width = 8.38
                    sheet.column_dimensions['P'].width = 8.38
                    sheet.column_dimensions['Q'].width = 8.38
                    sheet.column_dimensions['R'].width = 8.38
                    sheet.column_dimensions['S'].width = 8.38
                    sheet.column_dimensions['T'].width = 8.38
                    sheet.column_dimensions['U'].width = 8.38
                    sheet.column_dimensions['V'].width = 8.38
                    sheet.column_dimensions['W'].width = 8.38
                    sheet.column_dimensions['X'].width = 8.38
                    sheet.column_dimensions['Y'].width = 15.75
                    sheet.column_dimensions['Z'].width = 8.38
                    sheet.column_dimensions['AA'].width = 8.38
                    sheet.column_dimensions['AB'].width = 8.38
                    sheet.column_dimensions['AC'].width = 8.38
                    sheet.column_dimensions['AD'].width = 8.38
                    sheet.column_dimensions['AE'].width = 8.38
                    sheet.column_dimensions['AF'].width = 8.38
                    sheet.column_dimensions['AG'].width = 8.38
                    sheet.column_dimensions['AH'].width = 8.38
                    sheet.column_dimensions['AI'].width = 11.88
                    sheet.column_dimensions['AJ'].width = 11.88
                    sheet.column_dimensions['AK'].width = 11.88
                    sheet.column_dimensions['AL'].width = 11.88
                    sheet.column_dimensions['AM'].width = 11.88
                    sheet.column_dimensions['AN'].width = 11.88
                    sheet.column_dimensions['AO'].width = 11.88
                    sheet.column_dimensions['AP'].width = 11.88
                    sheet.column_dimensions['AQ'].width = 11.88
                    sheet.column_dimensions['AR'].width = 11.88
                    sheet.column_dimensions['AS'].width = 11.88
                    sheet.column_dimensions['AT'].width = 11.88
                    sheet.column_dimensions['AU'].width = 11.88
                    sheet.column_dimensions['AV'].width = 11.88
                    sheet.column_dimensions['AW'].width = 11.88
                    sheet.column_dimensions['AX'].width = 11.88
                    sheet.column_dimensions['AY'].width = 11.88
                    for row in sheet:
                        for cell in row:
                            sheet[cell.coordinate].font = font
            sheet1 = wb['Sheet']
            wb.remove(sheet1)
            wb.save(filename=f'文件/{self.lineEdit_4.text()}.xlsx')
        elif self.lineEdit.text() == '' and self.lineEdit_2.text() == '':
            list_1 = c1.split(',')
            for i in list_1:
                sheet = wb.create_sheet(f'{i}')
                self.Qtext_A(f'创建{i}完成，开始处理中！')
                sheet['B1'] = '学年'
                sheet['B1'].alignment = align

                sheet['C1'] = '第一学年'
                sheet['C1'].alignment = align

                sheet['A2'] = '序号'
                sheet['A2'].alignment = align
                sheet.merge_cells('A2:A4')

                sheet['B2'] = '年级班级'
                sheet.merge_cells('B2:B4')
                sheet['B2'].alignment = align

                sheet['C2'] = '学生姓名'
                sheet.merge_cells('C2:C4')
                sheet['C2'].alignment = align

                sheet['D2'] = '身份证号码'
                sheet.merge_cells('D2:D4')
                sheet['D2'].alignment = align

                sheet['E2'] = '性别'
                sheet.merge_cells('E2:E4')
                sheet['E2'].alignment = align

                sheet['F2'] = '年龄'
                sheet.merge_cells('F2:F4')
                sheet['F2'].alignment = align

                sheet['G2'] = '到检情况'
                sheet.merge_cells('G2:G4')
                sheet['G2'].alignment = align

                sheet['H2'] = '学籍号'
                sheet.merge_cells('H2:H4')
                sheet['H2'].alignment = align

                sheet['I2'] = '查期'
                sheet.merge_cells('I2:I4')
                sheet['I2'].alignment = align

                sheet['J2'] = '发育及营养状况'
                sheet.merge_cells('J2:J4')
                sheet['J2'].alignment = align

                sheet['K2'] = '身高(cm)'
                sheet.merge_cells('K2:K4')
                sheet['K2'].alignment = align

                sheet['L2'] = '体重(kg)'
                sheet.merge_cells('L2:L4')
                sheet['L2'].alignment = align

                sheet['M2'] = '胸围(cm)'
                sheet.merge_cells('M2:M4')
                sheet['M2'].alignment = align

                sheet['N2'] = '肺活量(ml)'
                sheet.merge_cells('N2:N4')
                sheet['N2'].alignment = align

                sheet['O2'] = '血压(mmhg)'
                sheet.merge_cells('O2:O4')
                sheet['O2'].alignment = align

                sheet['P2'] = '脉搏(次/分)'
                sheet.merge_cells('P2:P4')
                sheet['P2'].alignment = align

                sheet['Q2'] = '视力'
                sheet.merge_cells('Q2:V2')
                sheet['Q2'].alignment = align

                sheet['Q3'] = '左'
                sheet.merge_cells('Q3:S3')
                sheet['Q3'].alignment = align

                sheet['T3'] = '右'
                sheet.merge_cells('T3:V3')
                sheet['T3'].alignment = align

                sheet['Q4'] = '裸眼视力'
                sheet['Q4'].alignment = align

                sheet['R4'] = '矫正视力'
                sheet['R4'].alignment = align

                sheet['S4'] = '矫正度数'
                sheet['S4'].alignment = align

                sheet['T4'] = '裸眼视力'
                sheet['T4'].alignment = align

                sheet['U4'] = '矫正视力'
                sheet['U4'].alignment = align

                sheet['V4'] = '矫正度数'
                sheet['V4'].alignment = align

                sheet['W2'] = '听力'
                sheet.merge_cells('W2:X2')
                sheet['W2'].alignment = align

                sheet['W3'] = '左'
                sheet['W3'].alignment = align

                sheet['X3'] = '右'
                sheet['X3'].alignment = align

                sheet['Y2'] = '沙眼'
                sheet.merge_cells('Y2:Y4')
                sheet['Y2'].alignment = align

                sheet['Z2'] = '色觉'
                sheet.merge_cells('Z2:Z4')
                sheet['Z2'].alignment = align

                sheet['AA2'] = '鼻病'
                sheet.merge_cells('AA2:AA4')
                sheet['AA2'].alignment = align

                sheet['AB2'] = '口腔'
                sheet.merge_cells('AB2:AB4')
                sheet['AB2'].alignment = align

                sheet['AC2'] = '心脏'
                sheet.merge_cells('AC2:AC4')
                sheet['AC2'].alignment = align

                sheet['AD2'] = '肺'
                sheet.merge_cells('AD2:AD4')
                sheet['AD2'].alignment = align

                sheet['AE2'] = '肝脾'
                sheet.merge_cells('AE2:AE4')
                sheet['AE2'].alignment = align

                sheet['AF2'] = '胃肠'
                sheet.merge_cells('AF2:AF4')
                sheet['AF2'].alignment = align

                sheet['AG2'] = '脊柱'
                sheet.merge_cells('AG2:AG4')
                sheet['AG2'].alignment = align

                sheet['AH2'] = '四肢'
                sheet.merge_cells('AH2:AH4')
                sheet['AH2'].alignment = align

                sheet['AI2'] = '平足'
                sheet.merge_cells('AI2:AI4')
                sheet['AI2'].alignment = align

                sheet['AJ2'] = '皮肤'
                sheet.merge_cells('AJ2:AJ4')
                sheet['AJ2'].alignment = align

                sheet['AK2'] = '传染病'
                sheet.merge_cells('AK2:AK4')
                sheet['AK2'].alignment = align

                sheet['AL2'] = '胸透'
                sheet.merge_cells('AL2:AL4')
                sheet['AL2'].alignment = align

                sheet['AM2'] = '既往病史'
                sheet.merge_cells('AM2:AM4')
                sheet['AM2'].alignment = align

                sheet['AN2'] = '医院名或校医名'
                sheet.merge_cells('AN2:AN4')
                sheet['AN2'].alignment = align

                sheet['AO2'] = '头部'
                sheet.merge_cells('AO2:AO4')
                sheet['AO2'].alignment = align

                sheet['AP2'] = '颈部'
                sheet.merge_cells('AP2:AP4')
                sheet['AP2'].alignment = align

                sheet['AQ2'] = '胸部'
                sheet.merge_cells('AQ2:AQ4')
                sheet['AQ2'].alignment = align

                sheet['AR2'] = '淋巴'
                sheet.merge_cells('AR2:AR4')
                sheet['AR2'].alignment = align

                sheet['AS2'] = '扁桃体'
                sheet.merge_cells('AS2:AS4')
                sheet['AS2'].alignment = align

                sheet['AT2'] = 'BMI'
                sheet.merge_cells('AT2:AT4')
                sheet['AT2'].alignment = align

                sheet['AU2'] = 'BMI对比结果'
                sheet.merge_cells('AU2:AU4')
                sheet['AU2'].alignment = align

                sheet['AV2'] = '身高对比结果'
                sheet.merge_cells('AV2:AV4')
                sheet['AV2'].alignment = align

                sheet['AW2'] = '视力对比结果'
                sheet.merge_cells('AW2:AW4')
                sheet['AW2'].alignment = align

                sheet['AX2'] = '耳异常'
                sheet.merge_cells('AX2:AX4')
                sheet['AX2'].alignment = align

                sheet['AY2'] = '龋齿'
                sheet.merge_cells('AY2:AY4')
                sheet['AY2'].alignment = align

                sheet.row_dimensions[2].height = 19.5
                sheet.column_dimensions['A'].width = 8.38
                sheet.column_dimensions['B'].width = 11.88
                sheet.column_dimensions['C'].width = 14
                sheet.column_dimensions['D'].width = 20.13
                sheet.column_dimensions['E'].width = 15.63
                sheet.column_dimensions['F'].width = 7
                sheet.column_dimensions['G'].width = 7.5
                sheet.column_dimensions['H'].width = 7.5
                sheet.column_dimensions['I'].width = 7.5
                sheet.column_dimensions['J'].width = 7.5
                sheet.column_dimensions['K'].width = 7.5
                sheet.column_dimensions['L'].width = 11
                sheet.column_dimensions['M'].width = 8.38
                sheet.column_dimensions['N'].width = 8.38
                sheet.column_dimensions['O'].width = 8.38
                sheet.column_dimensions['P'].width = 8.38
                sheet.column_dimensions['Q'].width = 8.38
                sheet.column_dimensions['R'].width = 8.38
                sheet.column_dimensions['S'].width = 8.38
                sheet.column_dimensions['T'].width = 8.38
                sheet.column_dimensions['U'].width = 8.38
                sheet.column_dimensions['V'].width = 8.38
                sheet.column_dimensions['W'].width = 8.38
                sheet.column_dimensions['X'].width = 8.38
                sheet.column_dimensions['Y'].width = 15.75
                sheet.column_dimensions['Z'].width = 8.38
                sheet.column_dimensions['AA'].width = 8.38
                sheet.column_dimensions['AB'].width = 8.38
                sheet.column_dimensions['AC'].width = 8.38
                sheet.column_dimensions['AD'].width = 8.38
                sheet.column_dimensions['AE'].width = 8.38
                sheet.column_dimensions['AF'].width = 8.38
                sheet.column_dimensions['AG'].width = 8.38
                sheet.column_dimensions['AH'].width = 8.38
                sheet.column_dimensions['AI'].width = 11.88
                sheet.column_dimensions['AJ'].width = 11.88
                sheet.column_dimensions['AK'].width = 11.88
                sheet.column_dimensions['AL'].width = 11.88
                sheet.column_dimensions['AM'].width = 11.88
                sheet.column_dimensions['AN'].width = 11.88
                sheet.column_dimensions['AO'].width = 11.88
                sheet.column_dimensions['AP'].width = 11.88
                sheet.column_dimensions['AQ'].width = 11.88
                sheet.column_dimensions['AR'].width = 11.88
                sheet.column_dimensions['AS'].width = 11.88
                sheet.column_dimensions['AT'].width = 11.88
                sheet.column_dimensions['AU'].width = 11.88
                sheet.column_dimensions['AV'].width = 11.88
                sheet.column_dimensions['AW'].width = 11.88
                sheet.column_dimensions['AX'].width = 11.88
                sheet.column_dimensions['AY'].width = 11.88
                for row in sheet:
                    for cell in row:
                        sheet[cell.coordinate].font = font
            sheet1 = wb['Sheet']
            wb.remove(sheet1)
            wb.save(filename=f'文件/{self.lineEdit_4.text()}.xlsx')
        else:
            a1 = int(self.lineEdit.text()) + 1
            b1 = int(self.lineEdit_2.text()) + 1
            for i in range(1, a1):
                for y in range(1, b1):
                    sheet = wb.create_sheet(f'{self.lineEdit_5.text()}{i}年级{y}班')
                    self.Qtext_A(f'创建{i}年级{y}班完成，开始处理中！')
                    sheet['B1'] = '学年'
                    sheet['B1'].alignment = align

                    sheet['C1'] = '第一学年'
                    sheet['C1'].alignment = align

                    sheet['A2'] = '序号'
                    sheet['A2'].alignment = align
                    sheet.merge_cells('A2:A4')

                    sheet['B2'] = '年级班级'
                    sheet.merge_cells('B2:B4')
                    sheet['B2'].alignment = align

                    sheet['C2'] = '学生姓名'
                    sheet.merge_cells('C2:C4')
                    sheet['C2'].alignment = align

                    sheet['D2'] = '身份证号码'
                    sheet.merge_cells('D2:D4')
                    sheet['D2'].alignment = align

                    sheet['E2'] = '性别'
                    sheet.merge_cells('E2:E4')
                    sheet['E2'].alignment = align

                    sheet['F2'] = '年龄'
                    sheet.merge_cells('F2:F4')
                    sheet['F2'].alignment = align

                    sheet['G2'] = '到检情况'
                    sheet.merge_cells('G2:G4')
                    sheet['G2'].alignment = align

                    sheet['H2'] = '学籍号'
                    sheet.merge_cells('H2:H4')
                    sheet['H2'].alignment = align

                    sheet['I2'] = '查期'
                    sheet.merge_cells('I2:I4')
                    sheet['I2'].alignment = align

                    sheet['J2'] = '发育及营养状况'
                    sheet.merge_cells('J2:J4')
                    sheet['J2'].alignment = align

                    sheet['K2'] = '身高(cm)'
                    sheet.merge_cells('K2:K4')
                    sheet['K2'].alignment = align

                    sheet['L2'] = '体重(kg)'
                    sheet.merge_cells('L2:L4')
                    sheet['L2'].alignment = align

                    sheet['M2'] = '胸围(cm)'
                    sheet.merge_cells('M2:M4')
                    sheet['M2'].alignment = align

                    sheet['N2'] = '肺活量(ml)'
                    sheet.merge_cells('N2:N4')
                    sheet['N2'].alignment = align

                    sheet['O2'] = '血压(mmhg)'
                    sheet.merge_cells('O2:O4')
                    sheet['O2'].alignment = align

                    sheet['P2'] = '脉搏(次/分)'
                    sheet.merge_cells('P2:P4')
                    sheet['P2'].alignment = align

                    sheet['Q2'] = '视力'
                    sheet.merge_cells('Q2:V2')
                    sheet['Q2'].alignment = align

                    sheet['Q3'] = '左'
                    sheet.merge_cells('Q3:S3')
                    sheet['Q3'].alignment = align

                    sheet['T3'] = '右'
                    sheet.merge_cells('T3:V3')
                    sheet['T3'].alignment = align

                    sheet['Q4'] = '裸眼视力'
                    sheet['Q4'].alignment = align

                    sheet['R4'] = '矫正视力'
                    sheet['R4'].alignment = align

                    sheet['S4'] = '矫正度数'
                    sheet['S4'].alignment = align

                    sheet['T4'] = '裸眼视力'
                    sheet['T4'].alignment = align

                    sheet['U4'] = '矫正视力'
                    sheet['U4'].alignment = align

                    sheet['V4'] = '矫正度数'
                    sheet['V4'].alignment = align

                    sheet['W2'] = '听力'
                    sheet.merge_cells('W2:X2')
                    sheet['W2'].alignment = align

                    sheet['W3'] = '左'
                    sheet['W3'].alignment = align

                    sheet['X3'] = '右'
                    sheet['X3'].alignment = align

                    sheet['Y2'] = '沙眼'
                    sheet.merge_cells('Y2:Y4')
                    sheet['Y2'].alignment = align

                    sheet['Z2'] = '色觉'
                    sheet.merge_cells('Z2:Z4')
                    sheet['Z2'].alignment = align

                    sheet['AA2'] = '鼻病'
                    sheet.merge_cells('AA2:AA4')
                    sheet['AA2'].alignment = align

                    sheet['AB2'] = '口腔'
                    sheet.merge_cells('AB2:AB4')
                    sheet['AB2'].alignment = align

                    sheet['AC2'] = '心脏'
                    sheet.merge_cells('AC2:AC4')
                    sheet['AC2'].alignment = align

                    sheet['AD2'] = '肺'
                    sheet.merge_cells('AD2:AD4')
                    sheet['AD2'].alignment = align

                    sheet['AE2'] = '肝脾'
                    sheet.merge_cells('AE2:AE4')
                    sheet['AE2'].alignment = align

                    sheet['AF2'] = '胃肠'
                    sheet.merge_cells('AF2:AF4')
                    sheet['AF2'].alignment = align

                    sheet['AG2'] = '脊柱'
                    sheet.merge_cells('AG2:AG4')
                    sheet['AG2'].alignment = align

                    sheet['AH2'] = '四肢'
                    sheet.merge_cells('AH2:AH4')
                    sheet['AH2'].alignment = align

                    sheet['AI2'] = '平足'
                    sheet.merge_cells('AI2:AI4')
                    sheet['AI2'].alignment = align

                    sheet['AJ2'] = '皮肤'
                    sheet.merge_cells('AJ2:AJ4')
                    sheet['AJ2'].alignment = align

                    sheet['AK2'] = '传染病'
                    sheet.merge_cells('AK2:AK4')
                    sheet['AK2'].alignment = align

                    sheet['AL2'] = '胸透'
                    sheet.merge_cells('AL2:AL4')
                    sheet['AL2'].alignment = align

                    sheet['AM2'] = '既往病史'
                    sheet.merge_cells('AM2:AM4')
                    sheet['AM2'].alignment = align

                    sheet['AN2'] = '医院名或校医名'
                    sheet.merge_cells('AN2:AN4')
                    sheet['AN2'].alignment = align

                    sheet['AO2'] = '头部'
                    sheet.merge_cells('AO2:AO4')
                    sheet['AO2'].alignment = align

                    sheet['AP2'] = '颈部'
                    sheet.merge_cells('AP2:AP4')
                    sheet['AP2'].alignment = align

                    sheet['AQ2'] = '胸部'
                    sheet.merge_cells('AQ2:AQ4')
                    sheet['AQ2'].alignment = align

                    sheet['AR2'] = '淋巴'
                    sheet.merge_cells('AR2:AR4')
                    sheet['AR2'].alignment = align

                    sheet['AS2'] = '扁桃体'
                    sheet.merge_cells('AS2:AS4')
                    sheet['AS2'].alignment = align

                    sheet['AT2'] = 'BMI'
                    sheet.merge_cells('AT2:AT4')
                    sheet['AT2'].alignment = align

                    sheet['AU2'] = 'BMI对比结果'
                    sheet.merge_cells('AU2:AU4')
                    sheet['AU2'].alignment = align

                    sheet['AV2'] = '身高对比结果'
                    sheet.merge_cells('AV2:AV4')
                    sheet['AV2'].alignment = align

                    sheet['AW2'] = '视力对比结果'
                    sheet.merge_cells('AW2:AW4')
                    sheet['AW2'].alignment = align

                    sheet['AX2'] = '耳异常'
                    sheet.merge_cells('AX2:AX4')
                    sheet['AX2'].alignment = align

                    sheet['AY2'] = '龋齿'
                    sheet.merge_cells('AY2:AY4')
                    sheet['AY2'].alignment = align

                    sheet.row_dimensions[2].height = 19.5
                    sheet.column_dimensions['A'].width = 8.38
                    sheet.column_dimensions['B'].width = 11.88
                    sheet.column_dimensions['C'].width = 14
                    sheet.column_dimensions['D'].width = 20.13
                    sheet.column_dimensions['E'].width = 15.63
                    sheet.column_dimensions['F'].width = 7
                    sheet.column_dimensions['G'].width = 7.5
                    sheet.column_dimensions['H'].width = 7.5
                    sheet.column_dimensions['I'].width = 7.5
                    sheet.column_dimensions['J'].width = 7.5
                    sheet.column_dimensions['K'].width = 7.5
                    sheet.column_dimensions['L'].width = 11
                    sheet.column_dimensions['M'].width = 8.38
                    sheet.column_dimensions['N'].width = 8.38
                    sheet.column_dimensions['O'].width = 8.38
                    sheet.column_dimensions['P'].width = 8.38
                    sheet.column_dimensions['Q'].width = 8.38
                    sheet.column_dimensions['R'].width = 8.38
                    sheet.column_dimensions['S'].width = 8.38
                    sheet.column_dimensions['T'].width = 8.38
                    sheet.column_dimensions['U'].width = 8.38
                    sheet.column_dimensions['V'].width = 8.38
                    sheet.column_dimensions['W'].width = 8.38
                    sheet.column_dimensions['X'].width = 8.38
                    sheet.column_dimensions['Y'].width = 15.75
                    sheet.column_dimensions['Z'].width = 8.38
                    sheet.column_dimensions['AA'].width = 8.38
                    sheet.column_dimensions['AB'].width = 8.38
                    sheet.column_dimensions['AC'].width = 8.38
                    sheet.column_dimensions['AD'].width = 8.38
                    sheet.column_dimensions['AE'].width = 8.38
                    sheet.column_dimensions['AF'].width = 8.38
                    sheet.column_dimensions['AG'].width = 8.38
                    sheet.column_dimensions['AH'].width = 8.38
                    sheet.column_dimensions['AI'].width = 11.88
                    sheet.column_dimensions['AJ'].width = 11.88
                    sheet.column_dimensions['AK'].width = 11.88
                    sheet.column_dimensions['AL'].width = 11.88
                    sheet.column_dimensions['AM'].width = 11.88
                    sheet.column_dimensions['AN'].width = 11.88
                    sheet.column_dimensions['AO'].width = 11.88
                    sheet.column_dimensions['AP'].width = 11.88
                    sheet.column_dimensions['AQ'].width = 11.88
                    sheet.column_dimensions['AR'].width = 11.88
                    sheet.column_dimensions['AS'].width = 11.88
                    sheet.column_dimensions['AT'].width = 11.88
                    sheet.column_dimensions['AU'].width = 11.88
                    sheet.column_dimensions['AV'].width = 11.88
                    sheet.column_dimensions['AW'].width = 11.88
                    sheet.column_dimensions['AX'].width = 11.88
                    sheet.column_dimensions['AY'].width = 11.88
                    for row in sheet:
                        for cell in row:
                            sheet[cell.coordinate].font = font
            list_1 = c1.split(',')
            for i in list_1:
                sheet = wb.create_sheet(f'{i}')
                self.Qtext_A(f'创建{i}完成，开始处理中！')
                sheet['B1'] = '学年'
                sheet['B1'].alignment = align

                sheet['C1'] = '第一学年'
                sheet['C1'].alignment = align

                sheet['A2'] = '序号'
                sheet['A2'].alignment = align
                sheet.merge_cells('A2:A4')

                sheet['B2'] = '年级班级'
                sheet.merge_cells('B2:B4')
                sheet['B2'].alignment = align

                sheet['C2'] = '学生姓名'
                sheet.merge_cells('C2:C4')
                sheet['C2'].alignment = align

                sheet['D2'] = '身份证号码'
                sheet.merge_cells('D2:D4')
                sheet['D2'].alignment = align

                sheet['E2'] = '性别'
                sheet.merge_cells('E2:E4')
                sheet['E2'].alignment = align

                sheet['F2'] = '年龄'
                sheet.merge_cells('F2:F4')
                sheet['F2'].alignment = align

                sheet['G2'] = '到检情况'
                sheet.merge_cells('G2:G4')
                sheet['G2'].alignment = align

                sheet['H2'] = '学籍号'
                sheet.merge_cells('H2:H4')
                sheet['H2'].alignment = align

                sheet['I2'] = '查期'
                sheet.merge_cells('I2:I4')
                sheet['I2'].alignment = align

                sheet['J2'] = '发育及营养状况'
                sheet.merge_cells('J2:J4')
                sheet['J2'].alignment = align

                sheet['K2'] = '身高(cm)'
                sheet.merge_cells('K2:K4')
                sheet['K2'].alignment = align

                sheet['L2'] = '体重(kg)'
                sheet.merge_cells('L2:L4')
                sheet['L2'].alignment = align

                sheet['M2'] = '胸围(cm)'
                sheet.merge_cells('M2:M4')
                sheet['M2'].alignment = align

                sheet['N2'] = '肺活量(ml)'
                sheet.merge_cells('N2:N4')
                sheet['N2'].alignment = align

                sheet['O2'] = '血压(mmhg)'
                sheet.merge_cells('O2:O4')
                sheet['O2'].alignment = align

                sheet['P2'] = '脉搏(次/分)'
                sheet.merge_cells('P2:P4')
                sheet['P2'].alignment = align

                sheet['Q2'] = '视力'
                sheet.merge_cells('Q2:V2')
                sheet['Q2'].alignment = align

                sheet['Q3'] = '左'
                sheet.merge_cells('Q3:S3')
                sheet['Q3'].alignment = align

                sheet['T3'] = '右'
                sheet.merge_cells('T3:V3')
                sheet['T3'].alignment = align

                sheet['Q4'] = '裸眼视力'
                sheet['Q4'].alignment = align

                sheet['R4'] = '矫正视力'
                sheet['R4'].alignment = align

                sheet['S4'] = '矫正度数'
                sheet['S4'].alignment = align

                sheet['T4'] = '裸眼视力'
                sheet['T4'].alignment = align

                sheet['U4'] = '矫正视力'
                sheet['U4'].alignment = align

                sheet['V4'] = '矫正度数'
                sheet['V4'].alignment = align

                sheet['W2'] = '听力'
                sheet.merge_cells('W2:X2')
                sheet['W2'].alignment = align

                sheet['W3'] = '左'
                sheet['W3'].alignment = align

                sheet['X3'] = '右'
                sheet['X3'].alignment = align

                sheet['Y2'] = '沙眼'
                sheet.merge_cells('Y2:Y4')
                sheet['Y2'].alignment = align

                sheet['Z2'] = '色觉'
                sheet.merge_cells('Z2:Z4')
                sheet['Z2'].alignment = align

                sheet['AA2'] = '鼻病'
                sheet.merge_cells('AA2:AA4')
                sheet['AA2'].alignment = align

                sheet['AB2'] = '口腔'
                sheet.merge_cells('AB2:AB4')
                sheet['AB2'].alignment = align

                sheet['AC2'] = '心脏'
                sheet.merge_cells('AC2:AC4')
                sheet['AC2'].alignment = align

                sheet['AD2'] = '肺'
                sheet.merge_cells('AD2:AD4')
                sheet['AD2'].alignment = align

                sheet['AE2'] = '肝脾'
                sheet.merge_cells('AE2:AE4')
                sheet['AE2'].alignment = align

                sheet['AF2'] = '胃肠'
                sheet.merge_cells('AF2:AF4')
                sheet['AF2'].alignment = align

                sheet['AG2'] = '脊柱'
                sheet.merge_cells('AG2:AG4')
                sheet['AG2'].alignment = align

                sheet['AH2'] = '四肢'
                sheet.merge_cells('AH2:AH4')
                sheet['AH2'].alignment = align

                sheet['AI2'] = '平足'
                sheet.merge_cells('AI2:AI4')
                sheet['AI2'].alignment = align

                sheet['AJ2'] = '皮肤'
                sheet.merge_cells('AJ2:AJ4')
                sheet['AJ2'].alignment = align

                sheet['AK2'] = '传染病'
                sheet.merge_cells('AK2:AK4')
                sheet['AK2'].alignment = align

                sheet['AL2'] = '胸透'
                sheet.merge_cells('AL2:AL4')
                sheet['AL2'].alignment = align

                sheet['AM2'] = '既往病史'
                sheet.merge_cells('AM2:AM4')
                sheet['AM2'].alignment = align

                sheet['AN2'] = '医院名或校医名'
                sheet.merge_cells('AN2:AN4')
                sheet['AN2'].alignment = align

                sheet['AO2'] = '头部'
                sheet.merge_cells('AO2:AO4')
                sheet['AO2'].alignment = align

                sheet['AP2'] = '颈部'
                sheet.merge_cells('AP2:AP4')
                sheet['AP2'].alignment = align

                sheet['AQ2'] = '胸部'
                sheet.merge_cells('AQ2:AQ4')
                sheet['AQ2'].alignment = align

                sheet['AR2'] = '淋巴'
                sheet.merge_cells('AR2:AR4')
                sheet['AR2'].alignment = align

                sheet['AS2'] = '扁桃体'
                sheet.merge_cells('AS2:AS4')
                sheet['AS2'].alignment = align

                sheet['AT2'] = 'BMI'
                sheet.merge_cells('AT2:AT4')
                sheet['AT2'].alignment = align

                sheet['AU2'] = 'BMI对比结果'
                sheet.merge_cells('AU2:AU4')
                sheet['AU2'].alignment = align

                sheet['AV2'] = '身高对比结果'
                sheet.merge_cells('AV2:AV4')
                sheet['AV2'].alignment = align

                sheet['AW2'] = '视力对比结果'
                sheet.merge_cells('AW2:AW4')
                sheet['AW2'].alignment = align

                sheet['AX2'] = '耳异常'
                sheet.merge_cells('AX2:AX4')
                sheet['AX2'].alignment = align

                sheet['AY2'] = '龋齿'
                sheet.merge_cells('AY2:AY4')
                sheet['AY2'].alignment = align

                sheet.row_dimensions[2].height = 19.5
                sheet.column_dimensions['A'].width = 8.38
                sheet.column_dimensions['B'].width = 11.88
                sheet.column_dimensions['C'].width = 14
                sheet.column_dimensions['D'].width = 20.13
                sheet.column_dimensions['E'].width = 15.63
                sheet.column_dimensions['F'].width = 7
                sheet.column_dimensions['G'].width = 7.5
                sheet.column_dimensions['H'].width = 7.5
                sheet.column_dimensions['I'].width = 7.5
                sheet.column_dimensions['J'].width = 7.5
                sheet.column_dimensions['K'].width = 7.5
                sheet.column_dimensions['L'].width = 11
                sheet.column_dimensions['M'].width = 8.38
                sheet.column_dimensions['N'].width = 8.38
                sheet.column_dimensions['O'].width = 8.38
                sheet.column_dimensions['P'].width = 8.38
                sheet.column_dimensions['Q'].width = 8.38
                sheet.column_dimensions['R'].width = 8.38
                sheet.column_dimensions['S'].width = 8.38
                sheet.column_dimensions['T'].width = 8.38
                sheet.column_dimensions['U'].width = 8.38
                sheet.column_dimensions['V'].width = 8.38
                sheet.column_dimensions['W'].width = 8.38
                sheet.column_dimensions['X'].width = 8.38
                sheet.column_dimensions['Y'].width = 15.75
                sheet.column_dimensions['Z'].width = 8.38
                sheet.column_dimensions['AA'].width = 8.38
                sheet.column_dimensions['AB'].width = 8.38
                sheet.column_dimensions['AC'].width = 8.38
                sheet.column_dimensions['AD'].width = 8.38
                sheet.column_dimensions['AE'].width = 8.38
                sheet.column_dimensions['AF'].width = 8.38
                sheet.column_dimensions['AG'].width = 8.38
                sheet.column_dimensions['AH'].width = 8.38
                sheet.column_dimensions['AI'].width = 11.88
                sheet.column_dimensions['AJ'].width = 11.88
                sheet.column_dimensions['AK'].width = 11.88
                sheet.column_dimensions['AL'].width = 11.88
                sheet.column_dimensions['AM'].width = 11.88
                sheet.column_dimensions['AN'].width = 11.88
                sheet.column_dimensions['AO'].width = 11.88
                sheet.column_dimensions['AP'].width = 11.88
                sheet.column_dimensions['AQ'].width = 11.88
                sheet.column_dimensions['AR'].width = 11.88
                sheet.column_dimensions['AS'].width = 11.88
                sheet.column_dimensions['AT'].width = 11.88
                sheet.column_dimensions['AU'].width = 11.88
                sheet.column_dimensions['AV'].width = 11.88
                sheet.column_dimensions['AW'].width = 11.88
                sheet.column_dimensions['AX'].width = 11.88
                sheet.column_dimensions['AY'].width = 11.88
                for row in sheet:
                    for cell in row:
                        sheet[cell.coordinate].font = font
            sheet1 = wb['Sheet']
            wb.remove(sheet1)
            wb.save(filename=f'文件/{self.lineEdit_4.text()}.xlsx')

    def open_file(self):
        """
        按钮打开文件获取文件名及文件路径
        """
        filename, filetype = QFileDialog.getOpenFileName(self, "选取Excel文件", "./data", "Excel Files (*.xls *.xlsx)")
        self.Qtext_A(f'打开文件路径{filename}')
        # 将全局变量用来存储 文件路径
        self.filepath = filename
        # 将全局变量用来存储 处理以后的文件名
        self.fileName = self.filepath.split('/')[-1].split('.')[0]
        print(self.filepath is None)
        print(self.fileName is None)

    def biaoge_pl(self):
        workbook = load_workbook(filename=self.filepath)
        workbook1 = Workbook()
        sheet1 = workbook['Sheet1']['A']
        for i in sheet1:
            sheet = workbook1.create_sheet(f'{i.value}')
            self.Qtext_A(f'创建{i.value}完成')
            sheet.merge_cells("I1:K1")
            sheet.merge_cells("L1:X1")
            sheet.merge_cells("Y1:Z1")
            sheet.merge_cells("AA1:AD1")
            sheet.merge_cells("AE1:AH1")
            sheet.merge_cells("AI1:AL1")
            sheet.merge_cells("AM1:AN1")
            sheet.merge_cells("AO1:AS1")
            sheet['A1'].alignment = Alignment(horizontal="center")
            sheet['I1'] = '内科'
            sheet['I1'].alignment = Alignment(horizontal="center")
            sheet['L1'] = '外科体检结果'
            sheet['L1'].alignment = Alignment(horizontal="center")
            sheet['Y1'] = '矫正视力'
            sheet['Y1'].alignment = Alignment(horizontal="center")
            sheet['AA1'] = '裸视力'
            sheet['AA1'].alignment = Alignment(horizontal="center")
            sheet['AE1'] = '左眼'
            sheet['AE1'].alignment = Alignment(horizontal="center")
            sheet['AI1'] = '右眼'
            sheet['AI1'].alignment = Alignment(horizontal="center")
            sheet['AM1'] = '眼科'
            sheet['AM1'].alignment = Alignment(horizontal="center")
            sheet['AO1'] = '五官科'
            sheet['AO1'].alignment = Alignment(horizontal="center")
            sheet['A2'] = '录入者'
            sheet['A2'].alignment = Alignment(horizontal="center")
            sheet['B2'] = '班级'
            sheet['B2'].alignment = Alignment(horizontal="center")
            sheet['C2'] = '序号'
            sheet['C2'].alignment = Alignment(horizontal="center")
            sheet['D2'] = '姓名'
            sheet['D2'].alignment = Alignment(horizontal="center")
            sheet['E2'] = '性别'
            sheet['E2'].alignment = Alignment(horizontal="center")
            sheet['F2'] = '身份证号'
            sheet['F2'].alignment = Alignment(horizontal="center")
            sheet['G2'] = '到检情况'
            sheet['G2'].alignment = Alignment(horizontal="center")
            sheet['H2'] = '年龄'
            sheet['H2'].alignment = Alignment(horizontal="center")
            sheet['I2'] = '心'
            sheet['I2'].alignment = Alignment(horizontal="center")
            sheet['J2'] = '肺'
            sheet['J2'].alignment = Alignment(horizontal="center")
            sheet['K2'] = '肝/脾'
            sheet['K2'].alignment = Alignment(horizontal="center")
            sheet['L2'] = '身高(米)'
            sheet['L2'].alignment = Alignment(horizontal="center")
            sheet['M2'] = '体重(KG)'
            sheet['M2'].alignment = Alignment(horizontal="center")
            sheet['N2'] = 'BMI'
            sheet['N2'].alignment = Alignment(horizontal="center")
            sheet['O2'] = 'BMI修正'
            sheet['O2'].alignment = Alignment(horizontal="center")
            sheet['P2'] = 'BMI比对结果'
            sheet['P2'].alignment = Alignment(horizontal="center")
            sheet['Q2'] = '身高比对结果'
            sheet['Q2'].alignment = Alignment(horizontal="center")
            sheet['R2'] = '头部'
            sheet['R2'].alignment = Alignment(horizontal="center")
            sheet['S2'] = '颈部'
            sheet['S2'].alignment = Alignment(horizontal="center")
            sheet['T2'] = '胸部'
            sheet['T2'].alignment = Alignment(horizontal="center")
            sheet['U2'] = '脊柱'
            sheet['U2'].alignment = Alignment(horizontal="center")
            sheet['V2'] = '四肢'
            sheet['V2'].alignment = Alignment(horizontal="center")
            sheet['W2'] = '皮肤'
            sheet['W2'].alignment = Alignment(horizontal="center")
            sheet['X2'] = '淋巴结'
            sheet['X2'].alignment = Alignment(horizontal="center")
            sheet['Y2'] = '左'
            sheet['Y2'].alignment = Alignment(horizontal="center")
            sheet['Z2'] = '右'
            sheet['Z2'].alignment = Alignment(horizontal="center")
            sheet['AA2'] = '左'
            sheet['AA2'].alignment = Alignment(horizontal="center")
            sheet['AB2'] = '右'
            sheet['AB2'].alignment = Alignment(horizontal="center")
            sheet['AC2'] = '屈光'
            sheet['AC2'].alignment = Alignment(horizontal="center")
            sheet['AD2'] = '视力评价'
            sheet['AD2'].alignment = Alignment(horizontal="center")
            sheet['AE2'] = '球镜'
            sheet['AE2'].alignment = Alignment(horizontal="center")
            sheet['AF2'] = '柱镜'
            sheet['AF2'].alignment = Alignment(horizontal="center")
            sheet['AG2'] = '轴位'
            sheet['AG2'].alignment = Alignment(horizontal="center")
            sheet['AH2'] = '佩戴OK镜'
            sheet['AH2'].alignment = Alignment(horizontal="center")
            sheet['AI2'] = '球镜'
            sheet['AI2'].alignment = Alignment(horizontal="center")
            sheet['AJ2'] = '柱镜'
            sheet['AJ2'].alignment = Alignment(horizontal="center")
            sheet['AK2'] = '轴位'
            sheet['AK2'].alignment = Alignment(horizontal="center")
            sheet['AL2'] = '佩戴OK镜'
            sheet['AL2'].alignment = Alignment(horizontal="center")
            sheet['AM2'] = '结膜炎'
            sheet['AM2'].alignment = Alignment(horizontal="center")
            sheet['AN2'] = '沙眼'
            sheet['AN2'].alignment = Alignment(horizontal="center")
            sheet['AO2'] = '耳'
            sheet['AO2'].alignment = Alignment(horizontal="center")
            sheet['AP2'] = '鼻'
            sheet['AP2'].alignment = Alignment(horizontal="center")
            sheet['AQ2'] = '喉/扁桃体'
            sheet['AQ2'].alignment = Alignment(horizontal="center")
            sheet['AR2'] = '龋齿'
            sheet['AR2'].alignment = Alignment(horizontal="center")
            sheet['AS2'] = '牙周'
            sheet['AS2'].alignment = Alignment(horizontal="center")
            sheet['AT2'] = '肺活量'
            sheet['AT2'].alignment = Alignment(horizontal="center")
        sheet2 = workbook1['Sheet']
        workbook1.remove(sheet2)
        workbook1.save(filename=f'文件/{self.lineEdit_4.text()}.xlsx')
        return self.Qtext_A(f'{self.lineEdit_4.text()}文件创建完成')

    def Ns_school_pl(self):
        #                   水平对齐              垂直对齐            自动换行
        align = Alignment(horizontal="center", vertical="center", wrap_text=True)
        font = Font(name="宋体", size=9)
        workbook = load_workbook(filename=self.filepath)
        sheet1 = workbook['Sheet1']['A']
        wb = Workbook()
        for i in sheet1:
            sheet = wb.create_sheet(f'{i.value}')
            self.Qtext_A(f'开始创建{i.value}')
            sheet['B1'] = '学年'
            sheet['B1'].alignment = align

            sheet['C1'] = '第一学年'
            sheet['C1'].alignment = align

            sheet['A2'] = '序号'
            sheet['A2'].alignment = align
            sheet.merge_cells('A2:A4')

            sheet['B2'] = '年级班级'
            sheet.merge_cells('B2:B4')
            sheet['B2'].alignment = align

            sheet['C2'] = '学生姓名'
            sheet.merge_cells('C2:C4')
            sheet['C2'].alignment = align

            sheet['D2'] = '身份证号码'
            sheet.merge_cells('D2:D4')
            sheet['D2'].alignment = align

            sheet['E2'] = '性别'
            sheet.merge_cells('E2:E4')
            sheet['E2'].alignment = align

            sheet['F2'] = '年龄'
            sheet.merge_cells('F2:F4')
            sheet['F2'].alignment = align

            sheet['G2'] = '到检情况'
            sheet.merge_cells('G2:G4')
            sheet['G2'].alignment = align

            sheet['H2'] = '学籍号'
            sheet.merge_cells('H2:H4')
            sheet['H2'].alignment = align

            sheet['I2'] = '查期'
            sheet.merge_cells('I2:I4')
            sheet['I2'].alignment = align

            sheet['J2'] = '发育及营养状况'
            sheet.merge_cells('J2:J4')
            sheet['J2'].alignment = align

            sheet['K2'] = '身高(cm)'
            sheet.merge_cells('K2:K4')
            sheet['K2'].alignment = align

            sheet['L2'] = '体重(kg)'
            sheet.merge_cells('L2:L4')
            sheet['L2'].alignment = align

            sheet['M2'] = '胸围(cm)'
            sheet.merge_cells('M2:M4')
            sheet['M2'].alignment = align

            sheet['N2'] = '肺活量(ml)'
            sheet.merge_cells('N2:N4')
            sheet['N2'].alignment = align

            sheet['O2'] = '血压(mmhg)'
            sheet.merge_cells('O2:O4')
            sheet['O2'].alignment = align

            sheet['P2'] = '脉搏(次/分)'
            sheet.merge_cells('P2:P4')
            sheet['P2'].alignment = align

            sheet['Q2'] = '视力'
            sheet.merge_cells('Q2:V2')
            sheet['Q2'].alignment = align

            sheet['Q3'] = '左'
            sheet.merge_cells('Q3:S3')
            sheet['Q3'].alignment = align

            sheet['T3'] = '右'
            sheet.merge_cells('T3:V3')
            sheet['T3'].alignment = align

            sheet['Q4'] = '裸眼视力'
            sheet['Q4'].alignment = align

            sheet['R4'] = '矫正视力'
            sheet['R4'].alignment = align

            sheet['S4'] = '矫正度数'
            sheet['S4'].alignment = align

            sheet['T4'] = '裸眼视力'
            sheet['T4'].alignment = align

            sheet['U4'] = '矫正视力'
            sheet['U4'].alignment = align

            sheet['V4'] = '矫正度数'
            sheet['V4'].alignment = align

            sheet['W2'] = '听力'
            sheet.merge_cells('W2:X2')
            sheet['W2'].alignment = align

            sheet['W3'] = '左'
            sheet['W3'].alignment = align

            sheet['X3'] = '右'
            sheet['X3'].alignment = align

            sheet['Y2'] = '沙眼'
            sheet.merge_cells('Y2:Y4')
            sheet['Y2'].alignment = align

            sheet['Z2'] = '色觉'
            sheet.merge_cells('Z2:Z4')
            sheet['Z2'].alignment = align

            sheet['AA2'] = '鼻病'
            sheet.merge_cells('AA2:AA4')
            sheet['AA2'].alignment = align

            sheet['AB2'] = '口腔'
            sheet.merge_cells('AB2:AB4')
            sheet['AB2'].alignment = align

            sheet['AC2'] = '心脏'
            sheet.merge_cells('AC2:AC4')
            sheet['AC2'].alignment = align

            sheet['AD2'] = '肺'
            sheet.merge_cells('AD2:AD4')
            sheet['AD2'].alignment = align

            sheet['AE2'] = '肝脾'
            sheet.merge_cells('AE2:AE4')
            sheet['AE2'].alignment = align

            sheet['AF2'] = '胃肠'
            sheet.merge_cells('AF2:AF4')
            sheet['AF2'].alignment = align

            sheet['AG2'] = '脊柱'
            sheet.merge_cells('AG2:AG4')
            sheet['AG2'].alignment = align

            sheet['AH2'] = '四肢'
            sheet.merge_cells('AH2:AH4')
            sheet['AH2'].alignment = align

            sheet['AI2'] = '平足'
            sheet.merge_cells('AI2:AI4')
            sheet['AI2'].alignment = align

            sheet['AJ2'] = '皮肤'
            sheet.merge_cells('AJ2:AJ4')
            sheet['AJ2'].alignment = align

            sheet['AK2'] = '传染病'
            sheet.merge_cells('AK2:AK4')
            sheet['AK2'].alignment = align

            sheet['AL2'] = '胸透'
            sheet.merge_cells('AL2:AL4')
            sheet['AL2'].alignment = align

            sheet['AM2'] = '既往病史'
            sheet.merge_cells('AM2:AM4')
            sheet['AM2'].alignment = align

            sheet['AN2'] = '医院名或校医名'
            sheet.merge_cells('AN2:AN4')
            sheet['AN2'].alignment = align

            sheet['AO2'] = '头部'
            sheet.merge_cells('AO2:AO4')
            sheet['AO2'].alignment = align

            sheet['AP2'] = '颈部'
            sheet.merge_cells('AP2:AP4')
            sheet['AP2'].alignment = align

            sheet['AQ2'] = '胸部'
            sheet.merge_cells('AQ2:AQ4')
            sheet['AQ2'].alignment = align

            sheet['AR2'] = '淋巴'
            sheet.merge_cells('AR2:AR4')
            sheet['AR2'].alignment = align

            sheet['AS2'] = '扁桃体'
            sheet.merge_cells('AS2:AS4')
            sheet['AS2'].alignment = align

            sheet['AT2'] = 'BMI'
            sheet.merge_cells('AT2:AT4')
            sheet['AT2'].alignment = align

            sheet['AU2'] = 'BMI对比结果'
            sheet.merge_cells('AU2:AU4')
            sheet['AU2'].alignment = align

            sheet['AV2'] = '身高对比结果'
            sheet.merge_cells('AV2:AV4')
            sheet['AV2'].alignment = align

            sheet['AW2'] = '视力对比结果'
            sheet.merge_cells('AW2:AW4')
            sheet['AW2'].alignment = align

            sheet['AX2'] = '耳异常'
            sheet.merge_cells('AX2:AX4')
            sheet['AX2'].alignment = align

            sheet['AY2'] = '龋齿'
            sheet.merge_cells('AY2:AY4')
            sheet['AY2'].alignment = align

            sheet.row_dimensions[2].height = 19.5
            sheet.column_dimensions['A'].width = 8.38
            sheet.column_dimensions['B'].width = 11.88
            sheet.column_dimensions['C'].width = 14
            sheet.column_dimensions['D'].width = 20.13
            sheet.column_dimensions['E'].width = 15.63
            sheet.column_dimensions['F'].width = 7
            sheet.column_dimensions['G'].width = 7.5
            sheet.column_dimensions['H'].width = 7.5
            sheet.column_dimensions['I'].width = 7.5
            sheet.column_dimensions['J'].width = 7.5
            sheet.column_dimensions['K'].width = 7.5
            sheet.column_dimensions['L'].width = 11
            sheet.column_dimensions['M'].width = 8.38
            sheet.column_dimensions['N'].width = 8.38
            sheet.column_dimensions['O'].width = 8.38
            sheet.column_dimensions['P'].width = 8.38
            sheet.column_dimensions['Q'].width = 8.38
            sheet.column_dimensions['R'].width = 8.38
            sheet.column_dimensions['S'].width = 8.38
            sheet.column_dimensions['T'].width = 8.38
            sheet.column_dimensions['U'].width = 8.38
            sheet.column_dimensions['V'].width = 8.38
            sheet.column_dimensions['W'].width = 8.38
            sheet.column_dimensions['X'].width = 8.38
            sheet.column_dimensions['Y'].width = 15.75
            sheet.column_dimensions['Z'].width = 8.38
            sheet.column_dimensions['AA'].width = 8.38
            sheet.column_dimensions['AB'].width = 8.38
            sheet.column_dimensions['AC'].width = 8.38
            sheet.column_dimensions['AD'].width = 8.38
            sheet.column_dimensions['AE'].width = 8.38
            sheet.column_dimensions['AF'].width = 8.38
            sheet.column_dimensions['AG'].width = 8.38
            sheet.column_dimensions['AH'].width = 8.38
            sheet.column_dimensions['AI'].width = 11.88
            sheet.column_dimensions['AJ'].width = 11.88
            sheet.column_dimensions['AK'].width = 11.88
            sheet.column_dimensions['AL'].width = 11.88
            sheet.column_dimensions['AM'].width = 11.88
            sheet.column_dimensions['AN'].width = 11.88
            sheet.column_dimensions['AO'].width = 11.88
            sheet.column_dimensions['AP'].width = 11.88
            sheet.column_dimensions['AQ'].width = 11.88
            sheet.column_dimensions['AR'].width = 11.88
            sheet.column_dimensions['AS'].width = 11.88
            sheet.column_dimensions['AT'].width = 11.88
            sheet.column_dimensions['AU'].width = 11.88
            sheet.column_dimensions['AV'].width = 11.88
            sheet.column_dimensions['AW'].width = 11.88
            sheet.column_dimensions['AX'].width = 11.88
            sheet.column_dimensions['AY'].width = 11.88
            for row in sheet:
                for cell in row:
                    sheet[cell.coordinate].font = font
        sheet1 = wb['Sheet']
        wb.remove(sheet1)
        wb.save(filename=f'文件/{self.lineEdit_4.text()}.xlsx')
        self.Qtext_A(f'创建{self.lineEdit_4.text()}完成')

    def open_input_file(self):
        """
        按钮打开文件获取文件名及文件路径
        """
        filename1, filetype2 = QFileDialog.getOpenFileName(self, "选取Excel文件", "./data",
                                                           "Excel Files (*.xls *.xlsx)")
        self.Qtext_A(f'打开文件路径{filename1}')
        # 将全局变量用来存储 文件路径
        self.filepath_1 = filename1
        # 将全局变量用来存储 处理以后的文件名
        self.fileName_1 = self.filepath_1.split('/')[-1].split('.')[0]
        self.Qtext_A(f'获取到输入模版文件{self.filepath_1}')

    def open_pipei_file(self):
        """
        按钮打开文件获取文件名及文件路径
        """
        filename2, filetype3 = QFileDialog.getOpenFileName(self, "选取Excel文件", "./data",
                                                           "Excel Files (*.xls *.xlsx)")
        self.Qtext_A(f'打开匹配文件路径{filename2}')
        # 将全局变量用来存储 文件路径
        self.filepath_2 = filename2
        # 将全局变量用来存储 处理以后的文件名
        self.fileName_2 = self.filepath_2.split('/')[-1].split('.')[0]
        self.Qtext_A(f'获取到匹配文件{self.filepath_2}')

    def pipei_luru(self):
        """
        外层函数：遍历第一行到第二行 如果内容为目标内容，获取单元格列数，
        根据最大行来进行指定读取信息 组装信息传递给内层函数
        内层函数：跟外层函数功能一致，但是最后根据外层函数传递值进行写入
        :return:
        """
        row_value = {
            "序号": "",
            "姓名": "",
            "性别": "",
            "班级": "",
            "身份证号": "",
            "年龄": "",
        }
        workbook1 = load_workbook(filename=self.filepath_1)
        names = workbook1.sheetnames
        def write_file(value1):
            Serial_number1 = None
            name_column1 = None
            gender_column1 = None
            class_ban_column1 = None
            shenfenzheng_column1 = None
            age_column1 = None
            for na in names:
                sheet1 = workbook1[f'{na}']
                max_num = sheet1.max_row
                print(max_num)
                for cell in sheet1[1:3]:
                    for name in cell:
                        if name.value == '序号':
                            Serial_number1 = str(name.coordinate)[0]
                        elif name.value == '姓名':
                            name_column1 = str(name.coordinate)[0]
                        elif name.value == '性别':
                            gender_column1 = str(name.coordinate)[0]
                        elif name.value == '班级':
                            class_ban_column1 = str(name.coordinate)[0]
                        elif name.value == '身份证号':
                            shenfenzheng_column1 = str(name.coordinate)[0]
                        elif name.value == '年龄':
                            age_column1 = str(name.coordinate)[0]
                        else:
                            pass
                if value1["班级"].value == sheet1.title:
                    sheet1[f'{Serial_number1}{max_num + 1}'] = value1["序号"].value
                    sheet1[f'{name_column1}{max_num + 1}'] = value1["姓名"].value
                    sheet1[f'{gender_column1}{max_num + 1}'] = value1["性别"].value
                    sheet1[f'{class_ban_column1}{max_num + 1}'] = value1["班级"].value
                    sheet1[f'{shenfenzheng_column1}{max_num + 1}'] = value1["身份证号"].value
                    sheet1[f'{age_column1}{max_num + 1}'] = value1["年龄"].value
                    self.Qtext_A(f'序号：{value1["序号"].value}，姓名：{value1["姓名"].value}，性别：{value1["性别"].value}，班级：{value1["班级"].value}，身份证号：{value1["身份证号"].value}，年龄：{value1["年龄"].value}已加入工作簿')
                else:
                    break




        workbook = load_workbook(filename=self.filepath_2)
        sheet = workbook['Sheet1']
        Serial_number = None
        name_column = None
        gender_column = None
        class_ban_column = None
        shenfenzheng_column = None
        age_column = None
        for cell in sheet[1:2]:
            for name in cell:
                if name.value == '序号':
                    Serial_number = str(name.coordinate)[0]
                elif name.value == '姓名':
                    name_column = str(name.coordinate)[0]
                elif name.value == '性别':
                    gender_column = str(name.coordinate)[0]
                elif name.value == '班级':
                    class_ban_column = str(name.coordinate)[0]
                elif name.value == '身份证号':
                    shenfenzheng_column = str(name.coordinate)[0]
                elif name.value == '年龄':
                    age_column = str(name.coordinate)[0]
                else:
                    break
        max1 = sheet.max_row
        for i in range(2, max1 + 1):
            row_value["序号"] = sheet[f"{Serial_number}{i}"]
            row_value["姓名"] = sheet[f"{name_column}{i}"]
            row_value["性别"] = sheet[f"{gender_column}{i}"]
            row_value["班级"] = sheet[f"{class_ban_column}{i}"]
            row_value["身份证号"] = sheet[f"{shenfenzheng_column}{i}"]
            row_value["年龄"] = sheet[f"{age_column}{i}"]
            write_file(row_value)
        workbook1.save(filename=f'文件/{self.fileName_1}匹配版.xlsx')
        workbook1.close()
        workbook.close()
        self.Qtext_A(f'{self.fileName_1}创建完毕可以关闭程序了！')


if __name__ == '__main__':
    starttime = datetime.datetime.today()
    app = QApplication(sys.argv)
    ui = tebles()
    ui.show()
    sys.exit(app.exec_())
